#!/anaconda3/envs/FEALPy/bin python3.8
# -*- coding: utf-8 -*-
# File: iphone_data_statistics.py
# Author: Bryan SHEN
# E-mail: m18801919240_3@163.com
# Site: Shanghai, China
# Time: 2024/11/26 20:02
# File-Desp:

from sqlalchemy.engine import create_engine
import pandas as pd
import numpy as np
import datetime
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
import io
from mail_helper import MailHelper
from loguru import logger
from email.mime.image import MIMEImage
import os
from datetime import timedelta
import math
import re
import matplotlib.dates as mdates
import matplotlib.ticker as mtick

from matplotlib.ticker import MultipleLocator
from sqlalchemy import text

current_date = datetime.datetime.now().date()  # 获取当前日期
current_date_str = str(current_date)
date_before = current_date - timedelta(days=1)  # 获取昨天的数据日期
date_before_str = str(date_before)


# 连接 hive 数据库
engine = create_engine('presto://u_algorithm@trino.aihuishou.com:9090/hive/default')


# 新分城1组：基于型号的“30天询价量”、“30天成交量”、“昨天成交量“， 及各自的占比
query1 = """
    select 
        * 
    from  
        algo.calculate_phone_ratio_new_g1
    where 
        success_date = current_date + interval '-1' day
        and product_brand_name = '苹果'
    order by product_name
"""

# 新分城1组：<泛化到等级> 基于型号的“30天询价量”、“30天成交量”、“昨天成交量“， 及各自的占比
query2 = """
    select 
        * 
    from  
        algo.calculate_phone_ratio_extend_new_g1
    where 
        date(partition_date) = current_date
        and product_brand_name = '苹果'
    order by product_name
"""

# 2025-04-01 手机不同型号的 “提交、质检、转换” 数据表现情况
query3 = """
    select 
        * 
    from  
        algo.phone_submit_respect_convert_performace
    where 
        date(inspection_date) = current_date + interval '-1' day
        and product_brand_name = '苹果'
    order by product_name
"""

# 2025-04-01 前后两天的价格变化趋势分析（全渠道）
query4 = """
    /* 2025-04-07 过去14天数据（不包含当天）*/
    select
        base.*,
        case when dg1.effect_competitiveprice is not null then dg1.effect_competitiveprice
           else dg1.finalprice  
        end as effect_competitiveprice
    from 
        -- algo.system_new_g1_today_data_multisource base
        -- algo.system_new_g1_today_data base
        -- algo.system_new_g1_today_data_iphone base
        rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail base
    left join
    (
      select 
          *
      from
          dw.dw_source_daily_price
      where 
          date(partition_date) >= current_date + interval '-15' day
        AND ccgid = -1  -- 2024-10-31 -1代表不限城市
        AND scgid  = 219 -- 1代表京东渠道回收价
        AND product_category_id = 1
    ) dg1
    on date(base.partition_date) = date(dg1.partition_date) + interval '-1' day and base.product_sku_id = dg1.product_sku_id and base.product_level_id = dg1.product_level_id
    where  
        date(base.partition_date) between current_date + interval '-14' day and current_date + interval '-1' day
        and group_id = 219 -- 分城一组
        and product_brand_name = '苹果' 
        and base.product_category_id = 1
        
    UNION ALL
    
    /* 2025-04-07 当天实时数据（用昨天的finalprice和竞争力价格） */
    select
        base.*,
        case when dg1.effect_competitiveprice is not null then dg1.effect_competitiveprice
           else dg1.finalprice  
        end as effect_competitiveprice
    from 
        -- algo.system_new_g1_today_data_multisource base
        -- algo.system_new_g1_today_data base
        rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail base
    left join
    (
      select 
        *
      from
        dw.dw_source_daily_price
      where 
            date(partition_date) = current_date
        AND ccgid = -1  -- 2024-10-31 -1代表不限城市
        AND scgid  = 219 -- 1代表京东渠道回收价
        AND product_category_id = 1
    ) dg1
    on date(base.partition_date) = date(dg1.partition_date) and base.product_sku_id = dg1.product_sku_id and base.product_level_id = dg1.product_level_id
    where date(base.partition_date) = current_date
        and group_id = 219 -- 分城一组
        and product_brand_name = '苹果'
        and base.product_category_id = 1
"""

# 2025-04-02 转转ppv价格-自2024-12-30之后的核心sku价格走势
query5 = """
    select 
        partition_date,
        product_brand_name,
        product_level_name,
        product_sku_name,
        ppv,
        zz_pre_coupon_price,
        group_id
    from 
        rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail
    WHERE
        -- partition_date >= '2024-12-30'
         -- date(partition_date) between current_date + interval '-20' day and current_date + interval '-1' day
         date(partition_date) > current_date + interval '-20' day
        and group_id = 219 -- 分城1组
        and product_brand_name = '苹果'
        and product_category_id = 1
"""

# # 2025-04-02 转转ppv价格-自2024-12-30之后的核心sku价格走势
# query5 = """
#     select
#         partition_date,
#         product_brand_name,
#         product_level_name,
#         product_sku_name,
#         ppv,
#         zz_pre_coupon_price,
#         group_id
#     from
#         rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail
#     WHERE
#         -- partition_date >= '2024-12-30'
#          date(partition_date) between current_date + interval '-20' day and current_date + interval '-1' day
#         and group_id = 219 -- 分城1组
#         and product_brand_name = '苹果'
#         and product_category_id = 1
#
#     UNION ALL
#
#     -- 2025-04-14 加入'当天实时' 的爬价结果
#     select
#         partition_date,
#         product_brand_name,
#         product_level_name,
#         product_sku_name,
#         ppv,
#         zz_pre_coupon_price,
#         group_id
#     from
#         algo.system_new_g1_today_data_multisource
#     where
#         date(partition_date) = current_date
#         and group_id = 219 -- 分城1组
#         and product_brand_name = '苹果'
#         and product_category_id = 1
# """

# 2025-05-27 AHS价格-增加AHS的sku-level价格的走势图
query5_2 = """
    select
        distinct
        partition_date,
        product_name,
        product_sku_name,
        product_level_name,
        finalprice
    from 
        rpt.rpt_platform_trade_price_compass_compare_inquiry_ppv_detail
    WHERE
         group_id = 219   	
        and product_category_name = '手机'
        -- and partition_date >= '2025-05-01'
        and date(partition_date) > current_date + interval '-20' day
        and product_sku_name in (
            '苹果 iPhone 12 大陆国行 128G 保修时长＜30天 白色',
            '苹果 iPhone 13 大陆国行 128G 保修时长＜30天 星光色',
            '苹果 iPhone 13 Pro 大陆国行 保修时长＜30天 银色 256G',
            '苹果 iPhone 13 Pro Max 大陆国行 保修时长＜30天 银色 256G',
            '苹果 iPhone 14 大陆国行 128G 保修时长＜30天 午夜色',
            '苹果 iPhone 14 Pro 大陆国行 保修时长＜30天 256G 深空黑色',
            '苹果 iPhone 14 Pro Max 大陆国行 保修时长＜30天 256G 深空黑色',
            '苹果 iPhone 15 大陆国行 128G 保修时长＜30天 黑色',
            '苹果 iPhone 15 Pro 大陆国行 保修时长＜30天 256G 原色钛金属',
            '苹果 iPhone 15 Pro Max 大陆国行 保修时长＜30天 256G 原色钛金属',
            '苹果 iPhone 16 大陆国行 128G 黑色 保修时长≥330天',
            '苹果 iPhone 16 Pro 大陆国行 256G 保修时长≥330天 原色钛金属',
            '苹果 iPhone 16 Pro Max 大陆国行 256G 保修时长≥330天 原色钛金属'
        )
    order by partition_date desc, product_name
"""

# # 2025-05-28 ZZ的67个ppv监控价格（历史）
# zz_price_sql1 = """
#     SELECT
#         ppv0.activity_id,
#         ppv0.brand,
#         ppv0.product_name,
#         base.product_sku_name,
#         base.product_level_name,
#         -- base.ppv,
#         base.matched_zz_attributes,
#         ppv0.start_price,
#         ppv0.deal_price,
#         ppv0.create_dt,
#         ppv0.update_dt
#     FROM
#         algo.tmp_zz_detailled_67ppv_prices ppv0
#     JOIN
#         algo.tmp_matched_zz_ppv base
#     ON ppv0.activity_id = base.activity_id
#     WHERE ppv0.brand = '苹果'
# """

# 2025-05-28  ZZ的67个ppv监控价格（新增）
zz_price_sql2 = text("""
    SELECT
        ppv1.activity_id,
        ppv1.brand,
        ppv1.product_name,
        base.product_sku_name,
        base.product_level_name,
        -- base.ppv,
        base.matched_zz_attributes,
        ppv1.start_price,
        ppv1.deal_price,
        ppv1.create_dt,
        ppv1.update_dt
    FROM 
        ods.ods_stage_fact_crawl_platform_electronics_goods_info ppv1
    JOIN
  	    algo.tmp_matched_zz_ppv base
    ON ppv1.activity_id = base.activity_id
    WHERE 
        ppv1.from_web LIKE :pattern
        and ppv1.brand = '苹果'
        and date(ppv1.create_dt) >= current_date + interval '-14' day
""")


# 2025-07-31 转转B2C 过去14天 苹果核心SKU价格走势
query6 = """
    select * from algo.zz_b2c_iphone_sku_prices
"""

# 2025-09-01 爱回收券表更新
ahs_conpon_sql = """
    select
        *
    from 
        -- ods.ods_c2babtrader_competitive_price_coupon_rule
        ods.ods_c2babtrader_competitive_competitive_price_coupon_rule
    where category_id = 1
        and brand_id = 52
"""

# 2025-09-02 转转优惠券
zz_coupon_sql = """
    select 
        * 
    from 
        rpt.rpt_platform_trade_price_compass_coupon_package_zz
    where 
        ahs_category_id = 1
        and create_date = date '{}'
""".format(current_date_str)


# 2025-09-01 爱回收优惠券
# -----------爱回收优惠券去重逻辑-------------
def tidy_num(x):
    """Cast numeric-like values to int if whole, else rounded float. Keep None for NA."""

    if pd.isna(x):
        return None
    try:
        xf = float(x)
        xi = int(xf)
        return xi if abs(xf - xi) < 1e-9 else round(xf, 6)
    except Exception:
        return x

def compute_unique_sets(df: pd.DataFrame):

    # normalize columns
    df = df.copy()
    df.columns = [str(c).strip() for c in df.columns]
    required = {"package_code", "min_price_limit", "price"}
    missing = required - set(df.columns)
    if missing:
        raise ValueError(f"Missing required columns: {missing}")

    # keep only needed cols
    pairs = df[["package_code", "min_price_limit", "price"]].copy()
    pairs["min_price_limit"] = pairs["min_price_limit"].map(tidy_num)
    pairs["price"] = pairs["price"].map(tidy_num)
    pairs = pairs.dropna(subset=["min_price_limit", "price"])

    # signature per package: sorted unique pairs
    sig_by_pkg = (
        pairs.groupby("package_code", dropna=False)
             .apply(lambda g: tuple(sorted({(g["min_price_limit"].iloc[i], g["price"].iloc[i]) for i in range(len(g))})))
             .to_dict()
    )

    # invert: signature -> [packages]
    packages_by_signature = {}
    for pkg, sig in sig_by_pkg.items():
        packages_by_signature.setdefault(sig, []).append(pkg)

    return sig_by_pkg, packages_by_signature

def build_summary_df(packages_by_signature: dict) -> pd.DataFrame:

    rows = []
    for i, (sig, pkgs) in enumerate(packages_by_signature.items(), start=1):
        pairs_str = "; ".join([f"{a}->{b}" for a, b in sig])
        rows.append({
            "set_id": i,
            "pair_count": len(sig),
            "pairs": pairs_str,
            "package_codes": ", ".join(map(str, sorted(set(pkgs))))
        })
    return pd.DataFrame(rows).sort_values("set_id").reset_index(drop=True)
# -----------爱回收优惠券去重逻辑-------------

"""
价格竞争力邮件版 HTML 表格生成（完整版本）
    满足需求：
    1) 指标名称：BI价距、竞争力、费用率
    2) 数值格式：BI价距 → 80%（整数百分比）；竞争力/费用率 → 2.5%（一位小数）
    3) target 左列显示为 55% 等整数百分比；表头左上角为“型号”
    4) 型号列顺序：按 iPhone 代数升序，且同代内“标准款 → Pro → Pro Max”，
       也支持传入你自定义的顺序列表（仅保留 df 中存在的型号）
    5) BI价距可超过 100%（如 104%）：文本显示 104%，数据条宽度封顶 100%
"""
# ========= 工具函数 =========
def normalize_series_to_fraction(s: pd.Series):
    """
    把序列统一到 [0,1] 小数：
    - 若最大绝对值 > 1.5，判定源是 0~100 百分数 → /100
    - 否则认为已是 0~1 小数，原样返回
    - 非数值转为 NaN
    """
    s = pd.to_numeric(s, errors="coerce")
    if s.dropna().empty:
        return s
    max_abs = s.dropna().abs().max()
    return s / 100.0 if max_abs > 1.5 else s

def _render_bar_cell(val, color, fmt="0.0%", width_px=120, height_px=12):
    """
    单元格：数据条 + 文本。
    - 文本：使用原始值（可 > 100%）
    - 条形：仅用于可视化，宽度截断到 [0,1]，避免溢出
    fmt 支持 '0%'（整数百分比）或 '0.0%'（一位小数）
    """
    if pd.isna(val):
        return f"""<div style="width:{width_px}px;margin:0 auto;">
            <div style="width:{width_px}px;height:{height_px}px;background:#f3f3f3;border-radius:2px;"></div>
            <div style="font-size:12px;color:#666;margin-top:2px;">-</div></div>"""

    v_raw = float(val)                  # 原始值（可能 > 1.0）
    v_bar = max(0.0, min(v_raw, 1.0))   # 仅用于条形宽度

    # 文本格式
    if fmt == "0%":
        txt = f"{v_raw * 100:.0f}%"
    else:
        txt = f"{v_raw:.1%}"

    w = f"{v_bar * 100:.1f}%"

    return f"""<div style="width:{width_px}px;margin:0 auto;">
        <div style="width:{width_px}px;height:{height_px}px;background:#f3f3f3;border-radius:2px;">
            <div style="width:{w};height:{height_px}px;background:{color};border-radius:2px;"></div>
        </div>
        <div style="font-size:12px;color:#333;margin-top:2px;">{txt}</div>
    </div>"""

def _iphone_generation(name: str):
    """提取 iPhone 代数（12/13/14/15...）；失败返回极大值以置后。"""
    m = re.search(r"iPhone\s*(\d+)", str(name), re.I)
    return int(m.group(1)) if m else 10**9

def _is_pro_max(name: str):
    return re.search(r"Pro\s*Max", str(name), re.I) is not None

def _is_pro_only(name: str):
    return (re.search(r"\bPro\b", str(name), re.I) is not None) and (not _is_pro_max(name))

def preferred_iphone_order(product_names):
    """
    自动生成顺序：按代数升序分组；同代内 标准款 -> Pro -> Pro Max。
    仅返回传入列表中存在的型号（保持不重复）。
    """
    names = list(dict.fromkeys(product_names))
    buckets: dict[int, list[str]] = {}
    for n in names:
        buckets.setdefault(_iphone_generation(n), []).append(n)

    ordered = []
    for gen in sorted(buckets.keys()):
        group = buckets[gen]
        base = [x for x in group if (not _is_pro_only(x)) and (not _is_pro_max(x))]
        pro  = [x for x in group if _is_pro_only(x)]
        pmax = [x for x in group if _is_pro_max(x)]
        ordered.extend(base + pro + pmax)
    return ordered

def build_price_email_html(
    df: pd.DataFrame,
    metric_order = ("bi_ratio", "inquiry_compete_rate", "cost_rate_estimated"),
    metric_labels = {"bi_ratio": "BI价距", "inquiry_compete_rate": "竞争力", "cost_rate_estimated": "费用率"},
    metric_colors = {"bi_ratio": "#F4B183", "inquiry_compete_rate": "#9BBB59", "cost_rate_estimated": "#E57373"},
    metric_formats = {"bi_ratio": "0%", "inquiry_compete_rate": "0.0%", "cost_rate_estimated": "0.0%"},
    target_format = "0%",              # target 按 55% 效果展示（整数百分比）
    product_order= None
):
    """
    参数：
        df: 含列 product_name, target, inquiry_compete_rate, cost_rate_estimated, bi_ratio
        product_order: 自定义型号顺序（仅保留 df 中存在的型号），不传则自动按代数 + Pro 序

    返回：
        可直接嵌入邮件正文的 <table> HTML 字符串
    """
    required = {"product_name", "target", "inquiry_compete_rate", "cost_rate_estimated", "bi_ratio"}
    miss = required - set(df.columns)
    if miss:
        raise ValueError(f"缺少必要列：{miss}")

    df = df.copy()

    # 统一数值到 [0,1] 小数
    for c in ["target", "inquiry_compete_rate", "cost_rate_estimated", "bi_ratio"]:
        df[c] = normalize_series_to_fraction(df[c])

    # 行顺序（target）：优先 0~0.95（步长 0.05），否则按实际唯一值升序
    uniq_targets = sorted(df["target"].dropna().unique().tolist())
    canonical = [round(x, 4) for x in np.arange(0.0, 0.951, 0.05)]
    row_index = canonical if len(set(np.round(uniq_targets, 4)).intersection(set(np.round(canonical, 4)))) >= max(8, int(0.6 * len(canonical))) \
                else uniq_targets

    # 列顺序（product）：使用自定义顺序或自动顺序（仅保留 df 中存在的）
    if product_order is None:
        product_order = preferred_iphone_order(df["product_name"].dropna().drop_duplicates().tolist())
    else:
        present = set(df["product_name"].dropna().unique().tolist())
        product_order = [p for p in product_order if p in present]

    # 透视：(metric, product) → 调整成 (product, metric) 便于遍历
    agg_df = df.groupby(["target", "product_name"], as_index=False)[list(metric_order)].mean(numeric_only=True)
    pivot = agg_df.pivot(index="target", columns="product_name", values=list(metric_order)).reindex(index=row_index)
    pivot = pivot.reindex(columns=pd.MultiIndex.from_product([metric_order, product_order]))
    pivot = pivot.swaplevel(0, 1, axis=1)  # -> (product, metric)

    # ===== 生成 HTML =====
    html = []
    html.append('<table style="width:98%;border-collapse:collapse;margin:12px auto;font-family:Arial,Helvetica,sans-serif;">')

    # 表头-行1：左上角为“型号”；每个型号跨 3 列
    html.append('<tr>')
    html.append('<th style="border:1px solid #ddd;padding:8px;background:#edf3fe;">型号</th>')
    for prod in product_order:
        html.append(f'<th colspan="{len(metric_order)}" style="border:1px solid #ddd;padding:8px;background:#f2f2f2;font-weight:bold;">{prod}</th>')
    html.append('</tr>')

    # 表头-行2：左侧写 target；右侧写指标名
    html.append('<tr>')
    html.append('<th style="border:1px solid #ddd;padding:8px;background:#fafafa;">target</th>')
    for prod in product_order:
        for m in metric_order:
            label = metric_labels.get(m, m)
            html.append(f'<th style="border:1px solid #ddd;padding:8px;background:#fafafa;">{label}</th>')
    html.append('</tr>')

    # 数据行
    for t in row_index:
        if pd.isna(t):
            t_txt = "-"
        else:
            t_txt = f"{float(t) * 100:.0f}%" if target_format == "0%" else f"{float(t):.1%}"

        html.append('<tr>')
        # 左侧 target 列（背景浅蓝）
        html.append(f'<td style="border:1px solid #ddd;padding:8px;text-align:center;background:#edf3fe;font-weight:bold;">{t_txt}</td>')

        # 指标格子（数据条 + 数值）
        for prod in product_order:
            for m in metric_order:
                color = metric_colors.get(m, "#999")
                fmt   = metric_formats.get(m, "0.0%")
                val = pivot.loc[t, (prod, m)] if (prod, m) in pivot.columns else np.nan
                cell_html = _render_bar_cell(val, color, fmt=fmt)
                html.append(f'<td style="border:1px solid #ddd;padding:6px;text-align:center;">{cell_html}</td>')
        html.append('</tr>')

    html.append('</table>')
    return "".join(html)


# 发送邮件设置
def run_image(receiver_address='hongwei.shen@atrenew.com',
              mail_title=str(datetime.datetime.now().strftime('%Y_%m_%d')),
              font_path='../../data/font/simhei.ttf',
              run_date=str(datetime.date.today())):
    # try:
    font_path = 'font/SimHei.ttf'

    # receiver_address = "hongwei.shen@atrenew.com"
    receiver_address = "hongwei.shen@atrenew.com," \
               "alex.yang@atrenew.com," \
               "yi.luo2@atrenew.com," \
               "huiyu.tang@atrenew.com," \
               "qin.xu@atrenew.com," \
               "qifu.cai@atrenew.com"
    # receiver_address = "hongwei.shen@atrenew.com," \
    #            "qifu.cai@atrenew.com"
    mail = MailHelper("smtp.feishu.cn")   # 邮箱服务器类型
    mail.mail_sender_register("hongwei.shen@atrenew.com", "w4O9KetRQE0JaHtT")  # 发件人邮箱 授权码
    mail.msg_sender_name("沈红伟")  # 发件人姓名
    mail.mail_receiver_address(receiver_address)  # 收件人账号，为多个时英文逗号隔开
    mail.msg_title("<9点-实时爬价>🍎1组-苹果手机价格竞争力算法（0-100%）{}".format(mail_title))  # 邮件标题
    # mail.msg_title("<改券临时推送>🍎1组-苹果手机价格竞争力算法（0-100%）{}".format(mail_title))  # 邮件标题

    fm.fontManager.addfont(path=font_path)
    prop = fm.FontProperties(fname=font_path)

    plt.rcParams['font.sans-serif'] = prop.get_name()  # 设置中文字体为黑体
    plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题

    # 定义 HTML 表格的样式
    html_style = """
       <style>
           table {
               width: 80%;
               border-collapse: collapse;
               margin: 20px auto;
               font-family: Arial, sans-serif;
           }
           th, td {
               border: 1px solid #ddd;
               padding: 8px;
               text-align: center;
           }
           th {
               background-color: #f4f4f4;
               font-weight: bold;
           }
           tr:nth-child(even) {
               background-color: #f9f9f9;
           }
           tr:hover {
               background-color: #f1f1f1;
           }
       </style>
    """
    # 要按照这个顺序来绘制/图例排序
    levels_order = [
        "A+", "A-", "A1", "A", "A2",
        "B+1", "B+2", "B1", "B", "B2",
        "C+1", "C+", "C+2", "C1", "C", "C2",
        "D+1", "D+", "D+2", "D1", "D", "D2"
    ]
    target_skus = [
        # '苹果 iPhone 11 大陆国行 128G 保修时长＜30天 黑色 A2223',
        '苹果 iPhone 12 大陆国行 128G 保修时长＜30天 白色',
        '苹果 iPhone 13 大陆国行 128G 保修时长＜30天 星光色',
        '苹果 iPhone 13 Pro 大陆国行 保修时长＜30天 银色 256G',
        '苹果 iPhone 13 Pro Max 大陆国行 保修时长＜30天 银色 256G',
        '苹果 iPhone 14 大陆国行 128G 保修时长＜30天 午夜色',
        '苹果 iPhone 14 Pro 大陆国行 保修时长＜30天 256G 深空黑色',
        '苹果 iPhone 14 Pro Max 大陆国行 保修时长＜30天 256G 深空黑色',
        '苹果 iPhone 15 大陆国行 128G 保修时长＜30天 黑色',
        '苹果 iPhone 15 Pro 大陆国行 保修时长＜30天 256G 原色钛金属',
        '苹果 iPhone 15 Pro Max 大陆国行 保修时长＜30天 256G 原色钛金属',
        '苹果 iPhone 16 大陆国行 128G 黑色 保修时长≥330天',
        '苹果 iPhone 16 Pro 大陆国行 256G 保修时长≥330天 原色钛金属',
        '苹果 iPhone 16 Pro Max 大陆国行 256G 保修时长≥330天 原色钛金属'
    ]
    product_names = [
        '苹果 iPhone 11', '苹果 iPhone 12', '苹果 iPhone 13', '苹果 iPhone 14', '苹果 iPhone 15', '苹果 iPhone 16',
        '苹果 iPhone 11 Pro', '苹果 iPhone 12 Pro', '苹果 iPhone 13 Pro', '苹果 iPhone 14 Pro', '苹果 iPhone 15 Pro', '苹果 iPhone 16 Pro',
        '苹果 iPhone 11 Pro Max', '苹果 iPhone 12 Pro Max', '苹果 iPhone 13 Pro Max', '苹果 iPhone 14 Pro Max', '苹果 iPhone 15 Pro Max', '苹果 iPhone 16 Pro Max',
    ]

    # 2025-09-02 当日价格策略展示
    df = pd.read_excel('/data_hdd/bryan/bi_strategy_today_iphone_g1/collections/chosen_result_g1_{}.xlsx'.format(current_date_str))
    # 生成用于邮件的 HTML 表格
    html_table = build_price_email_html(df)
    html_style_strategy = """
           <style>
               table { width: 98%; border-collapse: collapse; margin: 12px auto; font-family: Arial, sans-serif; }
               th, td { border: 1px solid #ddd; padding: 8px; text-align: center; }
               th { background-color: #f4f4f4; font-weight: bold; }
               tr:nth-child(even) { background-color: #f9f9f9; }
           </style>
    """
    mail.msg_content(f"""
           <div align="center">
               <p style="color:#1677ff; font-size:18px; font-weight:700; margin:8px 0;">
                   🍎苹果手机价格竞争力算法（0-95%）
               </p>
           </div>
           {html_style_strategy}
           <div align="center">
               {html_table}
           </div>
    """)

    # 2025-09-01 爱回收-优惠券
    coupon_ahs = pd.read_sql(ahs_conpon_sql, engine)
    _, packages_by_signature = compute_unique_sets(coupon_ahs)    # 添加邮件内容
    summary_df = build_summary_df(packages_by_signature)
    mail.msg_content(f"""
       <div align="center">
           <p style="color:blue; font-size:18px;"><b>🍎爱回收苹果手机优惠券</b></p>
       </div>
       {html_style}  <!-- 插入表格样式 -->
       <div align="center">
           {summary_df.to_html(index=False, escape=False)}
       </div>
    """)  # 邮件内容

    # 2025-09-02 转转优惠券
    coupon_zz = pd.read_sql(zz_coupon_sql, engine)
    coupon_zz_filtered = coupon_zz[['coupon_type_name',  'price_range_min', 'price_range_max', 'coupon_price', 'coupon_desc', 'valid_time']]
    coupon_zz_filtered.rename(columns={
        'coupon_type_name': '券类型',
        'price_range_min': '价格下限',
        'price_range_max': '价格上限',
        'coupon_price': '优惠券额度',
        'coupon_desc': '优惠券描述',
        'valid_time': '优惠券有效期',
    }, inplace=True)

    # 添加邮件内容
    mail.msg_content(f"""
        <div align="center">
            <p style="color:blue; font-size:18px;"><b> 🍎转转手机优惠券 </b></p>
        </div>
        {html_style}  <!-- 插入表格样式 -->
        <div align="center">
            {coupon_zz_filtered.to_html(index=False, escape=False)}
        </div>
    """)  # 邮件内容

    # # 2025-05-28 新增转转67个ppv监控价格走势图，文件版本的历史记录
    # zz_67ppv_prices1 = pd.read_sql(zz_price_sql1, engine)
    # # 将历史数据中错误变更时间更新过来
    # # 确保create_dt是datetime类型（如果不是先转换）
    # zz_67ppv_prices1['create_dt'] = pd.to_datetime(zz_67ppv_prices1['create_dt'])
    # zz_67ppv_prices1['update_dt'] = pd.to_datetime(zz_67ppv_prices1['update_dt'])
    # # 定义要修改的时间范围
    # start_time = pd.Timestamp('2025-05-24 06:50:00')
    # end_time = pd.Timestamp('2025-05-24 07:59:59')
    # # 创建条件掩码
    # time_mask = (zz_67ppv_prices1['create_dt'] >= start_time) & (zz_67ppv_prices1['create_dt'] <= end_time)
    # # 修改符合条件的记录
    # zz_67ppv_prices1.loc[time_mask, 'create_dt'] = zz_67ppv_prices1.loc[time_mask, 'create_dt'].apply(
    #     lambda x: x.replace(hour=0, minute=0, second=0)
    # )

    # 2025-06-13 ppv变化价格日期太长 历史的zz_67ppv_prices1不需要了 调整为只用zz_67ppv_prices2中的最近14天数据
    zz_67ppv_prices2 = pd.read_sql(zz_price_sql2, engine, params={'pattern': '%test_zz_recycle_price%'})
    # 确保create_dt是datetime类型（如果不是先转换）
    zz_67ppv_prices2['create_dt'] = pd.to_datetime(zz_67ppv_prices2['create_dt'])
    zz_67ppv_prices2['update_dt'] = pd.to_datetime(zz_67ppv_prices2['update_dt'])
    # 合并历史和新增数据
    zz_67ppv_prices2['start_price'] = zz_67ppv_prices2['start_price'].fillna(0)  # 2025-06-09 防止没有爬到价格
    # zz_price_iphone = pd.concat([zz_67ppv_prices1, zz_67ppv_prices2], axis=0)
    # logger.info("zz_67ppv_prices1 : {} , zz_67ppv_prices2: {}, zz_price_hours: {}".format(len(zz_67ppv_prices1), len(zz_67ppv_prices2), len(zz_price_iphone)))
    zz_price_iphone = zz_67ppv_prices2
    # zz_price_iphone = zz_price[zz_price['brand'] == '苹果']
    zz_price_iphone['spider_time'] = pd.to_datetime(zz_price_iphone['create_dt'])
    zz_price_iphone['price'] = zz_price_iphone['start_price'].astype(int)
    zz_price_iphone = zz_price_iphone.sort_values(
        ['product_sku_name', 'product_level_name', 'spider_time']
    )
    fig, ax = plt.subplots(figsize=(16, 12))
    lines, labels = [], []

    # 文本标注向左偏移
    x_offset = pd.Timedelta(hours=2)

    for (sku, level), group in zz_price_iphone.groupby(['product_sku_name', 'product_level_name']):

        label = f"{sku} {level}"
        line, = ax.plot(
            group['spider_time'],
            group['price'],
            marker='o',
            markersize=4,
            markerfacecolor='none'
        )
        # 标记价格变化点（除首点外），用与线相同的颜色
        changes = group['price'].diff().fillna(0) != 0
        ax.scatter(
            group['spider_time'][changes],
            group['price'][changes],
            color=line.get_color(),
            s=60,
            marker='o'
        )

        # 标注变化前后两个点的价格
        for idx, changed in enumerate(changes.values):
            if not changed or idx == 0:
                continue
            prev_time, prev_price = group['spider_time'].iloc[idx - 1], group['price'].iloc[idx - 1]
            cur_time, cur_price = group['spider_time'].iloc[idx], group['price'].iloc[idx]
            ax.text(prev_time, prev_price, f"{prev_price}", ha='center', va='bottom', fontsize=10)
            ax.text(cur_time, cur_price, f"{cur_price}", ha='center', va='bottom', fontsize=10)

        # 第一条数据左侧添加 product_name
        first_time, first_price = group['spider_time'].iloc[0], group['price'].iloc[0]
        product_name = group['product_name'].iloc[0]
        ax.text(
            first_time - x_offset, first_price,
            product_name,
            ha='right', va='center',
            fontsize=10
        )

        lines.append(line)
        labels.append(label)

    # 半透明虚线网格
    ax.grid(which='both', linestyle='--', linewidth=0.5, alpha=0.5)

    # 每日竖线
    dates = sorted(zz_price_iphone['spider_time'].dt.normalize().unique())
    for d in dates:
        ax.axvline(x=d, linestyle='-', linewidth=1.0, color='grey', alpha=1.0)

    # 次刻度（上方）和主刻度（下方）
    minor_ticks, minor_labels = [], []
    for d in dates:
        # for h in [0, 6, 9, 12, 15, 18, 21]:
        for h in [0, 6, 12, 18]:
            minor_ticks.append(d + pd.Timedelta(hours=h))
            minor_labels.append(str(h))
    ax.set_xticks(minor_ticks, minor=True)
    ax.set_xticklabels(minor_labels, minor=True)

    major_ticks = dates
    major_labels = [d.strftime('%Y-%m-%d') for d in dates]
    ax.set_xticks(major_ticks)
    ax.set_xticklabels(major_labels)

    ax.tick_params(axis='x', which='minor', labeltop=True, labelbottom=False, rotation=0)
    ax.tick_params(axis='x', which='major', labeltop=False, labelbottom=True, rotation=45)

    ax.set_ylabel('ZZ PPV Price (CNY)')

    # 图例放右侧
    ax.legend(
        lines, labels,
        loc='center left', bbox_to_anchor=(1.02, 0.5),
        frameon=False
    )
    fig.subplots_adjust(right=0.82)
    ax.set_title("转转苹果手机不同ppv监控价格走势")
    plt.tight_layout()
    buf = io.BytesIO()
    plt.savefig(buf, format='png')
    buf.seek(0)  # 重置缓冲区指针
    plt.close()  # 关闭当前图表，释放资源
    # 添加图片到邮件
    mail.msg_image(image_id=0, image_name='ZZ苹果手机不同ppv监控价格走势', image=MIMEImage(buf.getvalue()))

    # 2025-07-31 转转B2C 过去14天 苹果核心SKU价格走势
    df6 = pd.read_sql(query6, engine)
    df6["date_dt"] = pd.to_datetime(df6['partition_date'])
    df6['sku'] = df6['title'] + " " + df6['product_level_name']
    df6 = df6.sort_values(['sku', "date_dt"])

    min_date = df6["date_dt"].min()
    max_date = df6["date_dt"].max()
    fig, ax = plt.subplots(figsize=(14, 7))
    lines, labels = [], []
    for sku, group in df6.groupby('sku'):
        # 折线
        line, = ax.plot(
            group["date_dt"],
            group['deal_price'],
            marker="o",
            markersize=4,
            markerfacecolor="none",
            label=sku
        )
        # 价格变化点
        changed = group['deal_price'].diff().fillna(0) != 0
        ax.scatter(group["date_dt"][changed],
                   group['deal_price'][changed],
                   s=60, marker="o")
        # 标注价格变化前后值
        for idx, is_changed in enumerate(changed):
            if idx == 0 or not is_changed:
                continue
            prev_t = group["date_dt"].iloc[idx - 1]
            prev_p = group['deal_price'].iloc[idx - 1]
            cur_t = group["date_dt"].iloc[idx]
            cur_p = group['deal_price'].iloc[idx]
            ax.text(prev_t, prev_p, f"{prev_p}", ha="center", va="bottom")
            ax.text(cur_t, cur_p, f"{cur_p}", ha="center", va="bottom")

        # 左侧标注型号名称（若无则回退到 SKU）
        label_name = group['product_name'].iloc[0] if 'product_name' in group.columns else sku
        first_t = group["date_dt"].iloc[0]
        first_p = group['deal_price'].iloc[0]
        ax.text(first_t, first_p, label_name, ha="right", va="center", rotation=45)
        lines.append(line)
        labels.append(sku)
    # 美化
    ax.set_title("苹果核心SKU-转转B2C端券后价趋势")
    ax.grid(True)
    # 每日竖线
    dates = sorted(df6["date_dt"].dt.normalize().unique())
    for d in dates:
        ax.axvline(x=d, color="grey", linestyle="-", linewidth=1.0, alpha=1.0)
    ax.set_xticks(dates)
    ax.set_xticklabels([pd.Timestamp(d).strftime("%Y-%m-%d") for d in dates], rotation=45, ha="right")

    ax.set_xlim(min_date - pd.Timedelta(days=2), max_date)
    ax.legend(lines, labels, loc="center left", bbox_to_anchor=(1.02, 0.5), frameon=False, title="SKU")

    plt.tight_layout()
    buf = io.BytesIO()
    plt.savefig(buf, format='png')
    buf.seek(0)  # 重置缓冲区指针
    plt.close()  # 关闭当前图表，释放资源
    # 添加图片到邮件
    mail.msg_image(image_id=1, image_name='转转B2C苹果手机不同SKU价格走势', image=MIMEImage(buf.getvalue()))

    # 2025-07-29 更新展示价格统一调整为券后价： 过去一段时间 核心sku的ZZ ppv券后价格走势
    df5 = pd.read_sql(query5, engine)
    # 2. 过滤数据并按日期排序
    df_filtered = (
        df5[df5['product_sku_name'].isin(target_skus)]
        .sort_values('partition_date')
    )

    # 2025-07-21 转转券逻辑更新
    zz_conpon_sql = """
        select 
            * 
        from 
            rpt.rpt_platform_trade_price_compass_coupon_package_zz
        where 
            -- clean_category = '手机'
            ahs_category_id = 1
            and create_date >= current_date + interval '-14' day
    """
    coupon_zz_all = pd.read_sql(zz_conpon_sql, engine)
    for date_str in df_filtered['partition_date'].drop_duplicates().sort_values(ascending=True):
        logger.info("------------ {} ---------------".format(date_str))

        target_day = pd.to_datetime(date_str).date()
        coupon_zz = coupon_zz_all[pd.to_datetime(coupon_zz_all['create_date']).dt.date == target_day]
        def calculate_zz_couponed_price(price: float) -> int:
            """
            根据 coupon_zz DataFrame 计算转转的券后价

            逻辑：
            1. 分别从 coupon_zz 中筛选“基础券”（coupon_type_name='基础券'）和“叠加券”（coupon_type_name='叠加券'）。
            2. 对每种券再分门槛券（coupon_price_type_id=0）和比例券（coupon_price_type_id=1）两类：
               — 门槛券：取所有 price_range_min <= price 的行，优惠额取 coupon_price 的最大值；
               — 比例券：取所有 price_range_min <= price 的行，比例取 coupon_price 的最大值，再计算 int(price * 比例) 作为优惠额。
            3. 每种券（基础券/叠加券）内部，门槛券和比例券的优惠额取最大值。
            4. 最终券后价 = price - 基础券优惠 - 叠加券优惠，结果向下取整整数返回。
            """
            # 假设 coupon_zz 已在作用域中，并且如下列：
            # ['create_date','ahs_category_id','coupon_type_id','coupon_type_name', 'coupon_price_type_id','price_range_operator','price_range_min', 'price_range_max','coupon_price']

            # 筛基础券和叠加券
            base_df = coupon_zz[coupon_zz['coupon_type_name'] == '基础券']
            added_df = coupon_zz[coupon_zz['coupon_type_name'] == '叠加券']
            def _threshold_discount(df):
                """ 门槛券 """
                thr = df[df['coupon_price_type_id'] == 0]
                # 取所有 price_range_min <= price 的行，coupon_price 最大值
                valid = thr[thr['price_range_min'] <= price]
                return float(valid['coupon_price'].max()) if not valid.empty else 0.0
            def _rate_discount(df):
                """ 比例券 """
                rate = df[df['coupon_price_type_id'] == 1]
                # 取所有 price_range_min <= price 的行，coupon_price 最大值
                valid = rate[rate['price_range_min'] <= price]
                if valid.empty:
                    return 0
                max_rate = float(valid['coupon_price'].max())
                return int(price * max_rate)  # 向下取整
            # 计算基础券优惠
            base_thr = _threshold_discount(base_df)  # 门槛券
            base_rate = _rate_discount(base_df)  # 比例券
            base_disc = max(base_thr, base_rate)
            # 计算叠加券优惠
            add_thr = _threshold_discount(added_df)
            add_rate = _rate_discount(added_df)
            add_disc = max(add_thr, add_rate)
            # 券后价 = price + 基础券优惠 + 叠加券优惠
            return int(price + base_disc + add_disc)
        mask = df_filtered['partition_date'] == date_str
        df_filtered.loc[mask, 'zz_couponed_price_18_added'] = (
            df_filtered.loc[mask, 'zz_pre_coupon_price'].apply(calculate_zz_couponed_price)
        )
    # 数据预处理：转换 partition_date 为日期格式
    df_filtered['partition_date'] = pd.to_datetime(df_filtered['partition_date'])

    # 2025-07-29 更新展示价格统一调整为券后价： 过去一段时间 AHS的sku-level券后价格的走势
    df5_2 = pd.read_sql(query5_2, engine)
    # 过滤数据并按日期排序
    ahs_price_filtered = (
        df5_2[df5_2['product_sku_name'].isin(target_skus)]
        .sort_values('partition_date')
    )

    # 2025-02-21 所有跟优惠券相关的逻辑，全部替换成实时计算的
    conpon_sql = """
        select 
            * 
        from 
            ods.ods_c2babtrader_competitive_price_coupon_detail
        where date(partition_date) >= current_date + interval '-14' day
    """.format(date_str)
    couponed_df_all = pd.read_sql(conpon_sql, engine)
    # 计算每个partition_date中每个finalprice对应的券后价
    for date_str in ahs_price_filtered['partition_date'].drop_duplicates().sort_values(ascending=True):
        logger.info("------------ {} ---------------".format(date_str))

        coupon_ahs = couponed_df_all[(couponed_df_all['config_id'] == 1) & (couponed_df_all['partition_date']) == date_str]

        def calculate_ahs_couponed_price(price):
            # 匹配爱回收的券数据
            # coupon_data = couponed_df[couponed_df['config_id'] == 1]
            coupon = 0
            rated_price = price
            for _, row in coupon_ahs.iterrows():
                if row['coupon_type'] == 0:  # 阶梯型券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        coupon = row['coupon_price']
                elif row['coupon_type'] == 1:  # 固定比例券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
            return coupon + rated_price
        mask2 = ahs_price_filtered['partition_date'] == date_str
        ahs_price_filtered.loc[mask2, 'ahs_couponed_price'] = (
            ahs_price_filtered.loc[mask2, 'finalprice'].apply(calculate_ahs_couponed_price)
        )
    # 数据预处理：转换 partition_date 为日期格式
    ahs_price_filtered['partition_date'] = pd.to_datetime(ahs_price_filtered['partition_date'])

    # ——— 子图初始化 ———
    fig, axs = plt.subplots(
        nrows=len(target_skus),
        ncols=2,
        figsize=(14, 4 * len(target_skus)),
        sharex=True
    )
    # 横坐标：每天一个刻度，年月日格式
    day_locator = mdates.DayLocator(interval=1)
    day_formatter = mdates.DateFormatter('%Y-%m-%d')

    for idx, sku in enumerate(target_skus):
        # — 左图：ZZ 的 ppv 价格走势 —
        ax_ppv = axs[idx, 0]
        df_sku_zz = df_filtered[df_filtered['product_sku_name'] == sku]

        # 画灰色线条（所有 ppv 轨迹）
        for ppv_val in df_sku_zz['ppv'].unique():
            sub = df_sku_zz[df_sku_zz['ppv'] == ppv_val]
            # ax_ppv.plot(sub['partition_date'], sub['zz_pre_coupon_price'], color='gray', alpha=0.5)
            ax_ppv.plot(sub['partition_date'], sub['zz_couponed_price_18_added'], color='gray', alpha=0.5)

        # 画中位数价格
        median_trend = (
            df_sku_zz
            # .groupby('partition_date')['zz_pre_coupon_price']
            .groupby('partition_date')['zz_couponed_price_18_added']
            .median()
            .reset_index()
        )
        ax_ppv.plot(median_trend['partition_date'],
                    # median_trend['zz_pre_coupon_price'],
                    median_trend['zz_couponed_price_18_added'],
                    color='blue', marker='o', linewidth=2,
                    label='中位数价格')

        ax_ppv.set_title(f"{sku} 的 ZZ-ppv 券后价走势")
        # ax_ppv.set_ylabel("zz_pre_coupon_price")
        ax_ppv.set_ylabel("zz_couponed_price_18_added")
        ax_ppv.legend()

        # 计算左图数据范围并加缓冲
        # min_y = df_sku_zz['zz_pre_coupon_price'].min()
        # max_y = df_sku_zz['zz_pre_coupon_price'].max()
        min_y = df_sku_zz['zz_couponed_price_18_added'].min()
        max_y = df_sku_zz['zz_couponed_price_18_added'].max()
        y_low, y_high = min_y - 10, max_y + 10

        ax_ppv.set_ylim(y_low, y_high)
        ax_ppv.yaxis.set_major_locator(MultipleLocator(100))
        ax_ppv.xaxis.set_major_locator(day_locator)
        ax_ppv.xaxis.set_major_formatter(day_formatter)
        plt.setp(ax_ppv.get_xticklabels(), rotation=45, ha='right')

        # — 右图：AHS sku-level 策略价走势 —
        ax_ahs = axs[idx, 1]
        df_sku_ahs = ahs_price_filtered[
            ahs_price_filtered['product_sku_name'] == sku
            ]
        # 画灰色线条（各 level 轨迹）
        for level in levels_order:
            sub = df_sku_ahs[
                df_sku_ahs['product_level_name'] == level
                ]
            if not sub.empty:
                # ax_ahs.plot(sub['partition_date'], sub['finalprice'], color='gray', alpha=0.5)
                ax_ahs.plot(sub['partition_date'], sub['ahs_couponed_price'], color='gray', alpha=0.5)
        # 画中位数价格
        median_ahs = (
            df_sku_ahs
            # .groupby('partition_date')['finalprice']
            .groupby('partition_date')['ahs_couponed_price']
            .median()
            .reset_index()
        )
        ax_ahs.plot(median_ahs['partition_date'],
                    # median_ahs['finalprice'],
                    median_ahs['ahs_couponed_price'],
                    color='red', marker='o', linewidth=2,
                    label='中位数价格')

        ax_ahs.set_title(f"{sku} 的 AHS sku-level 券后价走势")
        ax_ahs.set_ylabel("AHS sku-level 券后价")

        # 排序图例（可选）
        handles, labels = ax_ahs.get_legend_handles_labels()
        desired = levels_order + ['中位数价格']
        new_handles, new_labels = [], []
        for lbl in desired:
            if lbl in labels:
                i = labels.index(lbl)
                new_handles.append(handles[i])
                new_labels.append(labels[i])
        ax_ahs.legend(new_handles, new_labels)

        # **使用左图算好的同一组 y_low, y_high**
        ax_ahs.set_ylim(y_low, y_high)
        ax_ahs.yaxis.set_major_locator(MultipleLocator(100))
        ax_ahs.xaxis.set_major_locator(day_locator)
        ax_ahs.xaxis.set_major_formatter(day_formatter)
        plt.setp(ax_ahs.get_xticklabels(), rotation=45, ha='right')

    # 最后一行第二列添加 x 轴标签
    axs[-1, 1].set_xlabel("日期")

    # 调整布局以防止重叠
    plt.tight_layout()
    buf = io.BytesIO()
    plt.savefig(buf, format='png')
    buf.seek(0)  # 重置缓冲区指针
    plt.close()  # 关闭当前图表，释放资源
    # 添加图片到邮件
    mail.msg_image(image_id=2, image_name='<苹果核心SKU> ZZ的ppv券后价 vs AHS的sku-level券后价走势图', image=MIMEImage(buf.getvalue()))

    # 2025-04-01 前后两天的价格变化趋势分析
    df4 = pd.read_sql(query4, engine)

    df4 = df4[df4['finalprice'] != '(NULL)']
    df4 = df4[df4['effect_competitiveprice'] != '(NULL)']
    df4 = df4[df4['product_level_name'] != 'S']

    df4['success_nums'] = df4['success_nums'].fillna(0)
    df4['success_nums'] = df4['success_nums'].astype(int)
    df4['curr_success_nums'] = df4['curr_success_nums'].fillna(0)
    df4['curr_success_nums'] = df4['curr_success_nums'].astype(int)
    df4['inquiry_nums'] = df4['inquiry_nums'].fillna(0)
    df4['inquiry_nums'] = df4['inquiry_nums'].astype(int)

    # # 2025-04-14 <自营：门店+官网>渠道
    # df4['success_nums_so'] = df4['success_nums_so'].fillna(0)
    # df4['success_nums_so'] = df4['success_nums_so'].astype(int)
    # df4['curr_success_nums_so'] = df4['curr_success_nums_so'].fillna(0)
    # df4['curr_success_nums_so'] = df4['curr_success_nums_so'].astype(int)
    # df4['inquiry_nums_so'] = df4['inquiry_nums_so'].fillna(0)
    # df4['inquiry_nums_so'] = df4['inquiry_nums_so'].astype(int)
    #
    # # 2025-04-14 <京东+其他BD>渠道
    # df4['success_nums_jd'] = df4['success_nums_jd'].fillna(0)
    # df4['success_nums_jd'] = df4['success_nums_jd'].astype(int)
    # df4['curr_success_nums_jd'] = df4['curr_success_nums_jd'].fillna(0)
    # df4['curr_success_nums_jd'] = df4['curr_success_nums_jd'].astype(int)
    # df4['inquiry_nums_jd'] = df4['inquiry_nums_jd'].fillna(0)
    # df4['inquiry_nums_jd'] = df4['inquiry_nums_jd'].astype(int)

    compete_by_day_product = []

    for date_str in df4['partition_date'].drop_duplicates().sort_values(ascending=True):

        df = df4[df4['partition_date'] == date_str]

        logger.info("------------ {} ---------------".format(date_str))

        # 2025-02-21 所有跟优惠券相关的逻辑，全部替换成实时计算的
        conpon_sql = """
            select 
                * 
            from 
                ods.ods_c2babtrader_competitive_price_coupon_detail
            where partition_date = '{}'
        """.format(date_str)
        couponed_df = pd.read_sql(conpon_sql, engine)
        coupon_ahs = couponed_df[couponed_df['config_id'] == 1]
        coupon_zz = couponed_df[couponed_df['config_id'] == 3]

        def calculate_ahs_couponed_price(price):
            # 匹配爱回收的券数据
            # coupon_data = couponed_df[couponed_df['config_id'] == 1]
            coupon = 0
            rated_price = price
            for _, row in coupon_ahs.iterrows():
                if row['coupon_type'] == 0:  # 阶梯型券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        coupon = row['coupon_price']
                elif row['coupon_type'] == 1:  # 固定比例券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
            return coupon + rated_price

        def calculate_zz_couponed_price(price):
            # 匹配转转的券数据
            # coupon_data = couponed_df[couponed_df['config_id'] == 3]
            coupon = 0
            rated_price = price
            for _, row in coupon_zz.iterrows():
                if row['coupon_type'] == 1:  # 固定比例券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
                elif row['coupon_type'] == 0:  # 阶梯型券
                    if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                        coupon = row['coupon_price']
            return coupon + rated_price

        df['ahs_couponed_price'] = df['finalprice'].apply(calculate_ahs_couponed_price)  # 计算AHS券后价
        df['effect_couponed_price'] = df['effect_competitiveprice'].apply(calculate_ahs_couponed_price)  # 计算AHS竞争券后价
        df['zz_couponed_price_18_added'] = df['zz_pre_coupon_price'].apply(calculate_zz_couponed_price)  # 计算转转券后价

        # 裸价竞争力数和裸价竞争力
        df['naked_compete_nums'] = df.apply(lambda x: x['inquiry_nums'] if x['ahs_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
        # df['naked_compete_nums_so'] = df.apply(lambda x: x['inquiry_nums_so'] if x['ahs_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
        # df['naked_compete_nums_jd'] = df.apply(lambda x: x['inquiry_nums_jd'] if x['ahs_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

        naked_compete_rate_by_day = sum(df['naked_compete_nums']) * 1.000 / sum(df['inquiry_nums']) if sum(df['inquiry_nums']) > 0 else 0
        # naked_compete_rate_by_day_so = sum(df['naked_compete_nums_so']) * 1.000 / sum(df['inquiry_nums_so']) if sum(df['inquiry_nums_so']) > 0 else 0
        # naked_compete_rate_by_day_jd = sum(df['naked_compete_nums_jd']) * 1.000 / sum(df['inquiry_nums_jd']) if sum(df['inquiry_nums_jd']) > 0 else 0

        # 实际竞争力数和实际竞争力
        df['real_compete_nums'] = df.apply(lambda x: x['inquiry_nums'] if x['effect_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
        # df['real_compete_nums_so'] = df.apply(lambda x: x['inquiry_nums_so'] if x['effect_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)
        # df['real_compete_nums_jd'] = df.apply(lambda x: x['inquiry_nums_jd'] if x['effect_couponed_price'] >= x['zz_couponed_price_18_added'] else 0, axis=1)

        real_compete_rate_by_day = sum(df['real_compete_nums']) * 1.000 / sum(df['inquiry_nums']) if sum(df['inquiry_nums']) > 0 else 0
        # real_compete_rate_by_day_so = sum(df['real_compete_nums_so']) * 1.000 / sum(df['inquiry_nums_so']) if sum(df['inquiry_nums_so']) > 0 else 0
        # real_compete_rate_by_day_jd = sum(df['real_compete_nums_jd']) * 1.000 / sum(df['inquiry_nums_jd']) if sum(df['inquiry_nums_jd']) > 0 else 0

        for product_name in product_names:

            df_product = df[df['product_name'] == product_name]

            naked_compete_rate_by_product = sum(df_product['naked_compete_nums']) * 1.000 / sum(df_product['inquiry_nums']) if sum(df_product['inquiry_nums']) > 0 else 0
            real_compete_rate_by_product = sum(df_product['real_compete_nums']) * 1.000 / sum(df_product['inquiry_nums']) if sum(df_product['inquiry_nums']) > 0 else 0

            # naked_compete_rate_by_product_so = sum(df_product['naked_compete_nums_so']) * 1.000 / sum(df_product['inquiry_nums_so']) if sum(df_product['inquiry_nums_so']) > 0 else 0
            # real_compete_rate_by_product_so = sum(df_product['real_compete_nums_so']) * 1.000 / sum(df_product['inquiry_nums_so']) if sum(df_product['inquiry_nums_so']) > 0 else 0
            #
            # naked_compete_rate_by_product_jd = sum(df_product['naked_compete_nums_jd']) * 1.000 / sum(df_product['inquiry_nums_jd']) if sum(df_product['inquiry_nums_jd']) > 0 else 0
            # real_compete_rate_by_product_jd = sum(df_product['real_compete_nums_jd']) * 1.000 / sum(df_product['inquiry_nums_jd']) if sum(df_product['inquiry_nums_jd']) > 0 else 0

            compete_by_day_product.append(
                {
                    'partition_date': date_str,

                    # <全部> 裸价竞争力+实际竞争力
                    'naked_compete_rate_by_day': naked_compete_rate_by_day,
                    'real_compete_rate_by_day': real_compete_rate_by_day,
                    # 'naked_compete_rate_by_day_so': naked_compete_rate_by_day_so,
                    # 'real_compete_rate_by_day_so': real_compete_rate_by_day_so,
                    # 'naked_compete_rate_by_day_jd': naked_compete_rate_by_day_jd,
                    # 'real_compete_rate_by_day_jd': real_compete_rate_by_day_jd,

                    'product_name': product_name,

                    # <型号> 裸价竞争力+实际竞争力
                    'naked_compete_rate_by_product': naked_compete_rate_by_product,
                    'real_compete_rate_by_product': real_compete_rate_by_product,
                    # 'naked_compete_rate_by_product_so': naked_compete_rate_by_product_so,
                    # 'real_compete_rate_by_product_so': real_compete_rate_by_product_so,
                    # 'naked_compete_rate_by_product_jd': naked_compete_rate_by_product_jd,
                    # 'real_compete_rate_by_product_jd': real_compete_rate_by_product_jd
                }
            )

    df4_compete = pd.DataFrame(compete_by_day_product)
    # df4_compete.to_excel('df4_compete.xlsx', index=False)

    # --- 图1：按日期展示竞争力变化曲线 ---
    # 对 partition_date 去重（保留每个日期的第一条记录）
    df_day = df4_compete.drop_duplicates(subset='partition_date')

    plt.figure(figsize=(10, 6))

    # 定义3组颜色，依次用于【全部】、【自营】、【京东】
    # colors = ['blue', 'green', 'red']
    # 2025-04-29 更美观的对比色：使用 matplotlib 自带的“tab10”调色板前 3 色
    cmap = plt.get_cmap('tab10')
    colors = cmap.colors[:3]  # 比如 [(0.121,0.466,0.705), (1.0,0.498,0.0549), (0.1725,0.627,0.1725)]

    # 绘制【全部】：裸价竞争力 + 实际竞争力
    plt.plot(df_day['partition_date'], df_day['naked_compete_rate_by_day'], linestyle='--', marker='o', label='裸价竞争力 (全渠道)', color=colors[0])
    plt.plot(df_day['partition_date'], df_day['real_compete_rate_by_day'], linestyle='-', marker='o', label='实际竞争力 (全渠道)', color=colors[0])

    # # 绘制【自营】：裸价竞争力 + 实际竞争力
    # plt.plot(df_day['partition_date'], df_day['naked_compete_rate_by_day_so'], linestyle='--', marker='o', label='裸价竞争力 (自营)', color=colors[1])
    # plt.plot(df_day['partition_date'], df_day['real_compete_rate_by_day_so'], linestyle='-', marker='o', label='实际竞争力 (自营)', color=colors[1])
    #
    # # 绘制【京东】：裸价竞争力 + 实际竞争力
    # plt.plot(df_day['partition_date'], df_day['naked_compete_rate_by_day_jd'], linestyle='--', marker='o', label='裸价竞争力 (京东)', color=colors[2])
    # plt.plot(df_day['partition_date'], df_day['real_compete_rate_by_day_jd'], linestyle='-', marker='o', label='实际竞争力 (京东)', color=colors[2])

    # 设置 y 轴范围为 0 到 1（即 0%-100%），刻度以 0.1（10%）为间隔
    plt.ylim(0, 1)
    plt.yticks(np.arange(0, 1.01, 0.1))
    plt.gca().yaxis.set_major_formatter(mtick.PercentFormatter(1.0))

    # 添加横纵网格（虚线，透明度 0.5）
    plt.grid(True, which='both', linestyle='--', alpha=0.5)

    plt.xlabel('日期')
    plt.ylabel('竞争力')
    plt.title('竞争力(裸价 vs 实际) 随日期变化曲线')
    plt.xticks(rotation=45)

    # —— 分散标注，避免重叠 —— 为每一条线设置不同的偏移量 (dx, dy)
    offsets = {
        'naked_all': (3, 3),
        'real_all': (3, -8),
        # 'naked_so': (-5, 5),
        # 'real_so': (-5, -10),
        # 'naked_jd': (3, 5),
        # 'real_jd': (3, -10),
    }

    def annotate_series(x_series, y_series, color, key):
        dx, dy = offsets[key]
        for x, y in zip(x_series, y_series):
            plt.annotate(f'{y:.1%}',
                         xy=(x, y),
                         xytext=(dx, dy),
                         textcoords='offset points',
                         fontsize=9,
                         ha='center', va='bottom',
                         color=color,
                         # bbox=dict(boxstyle='round,pad=0.2', fc='white', alpha=0.6, linewidth=0) # 标注上的半透明边框
                         )

    ### 标注
    # 全渠道
    annotate_series(df_day['partition_date'], df_day['naked_compete_rate_by_day'], colors[0], 'naked_all')
    annotate_series(df_day['partition_date'], df_day['real_compete_rate_by_day'], colors[0], 'real_all')
    # # 自营
    # annotate_series(df_day['partition_date'], df_day['naked_compete_rate_by_day_so'], colors[1], 'naked_so')
    # annotate_series(df_day['partition_date'], df_day['real_compete_rate_by_day_so'], colors[1], 'real_so')
    # # 京东
    # annotate_series(df_day['partition_date'], df_day['naked_compete_rate_by_day_jd'], colors[2], 'naked_jd')
    # annotate_series(df_day['partition_date'], df_day['real_compete_rate_by_day_jd'], colors[2], 'real_jd')

    plt.legend()
    plt.tight_layout()

    buf = io.BytesIO()
    plt.savefig(buf, format='png')
    buf.seek(0)  # 重置缓冲区指针
    plt.close()  # 关闭当前图表，释放资源
    # 添加图片到邮件
    mail.msg_image(image_id=3, image_name='苹果手机-过去7天<裸价竞争力>和<实际竞争力>对比图', image=MIMEImage(buf.getvalue()))

    # 如果 partition_date 字段为字符串格式，需要先转换为 datetime 类型
    if df4_compete['partition_date'].dtype == 'O':
        df4_compete['partition_date'] = pd.to_datetime(df4_compete['partition_date'])
    # 获取今天和昨天的日期（日期部分）
    today = pd.Timestamp('today').normalize()  # 今天
    yesterday = today - pd.Timedelta(days=1)  # 昨天
    # 筛选出今天和昨天的数据
    df_today = df4_compete[df4_compete['partition_date'] == today]
    df_yesterday = df4_compete[df4_compete['partition_date'] == yesterday]
    # 对昨天的数据提取 product_name、naked_compete_rate_by_product 和 real_compete_rate_by_product
    df_yesterday_subset = df_yesterday[['product_name', 'naked_compete_rate_by_product', 'real_compete_rate_by_product']].copy()
    # df_yesterday_subset = df_yesterday[['product_name', 'naked_compete_rate_by_product', 'real_compete_rate_by_product', 'naked_compete_rate_by_product_so', 'real_compete_rate_by_product_so', 'naked_compete_rate_by_product_jd', 'real_compete_rate_by_product_jd']].copy()
    df_yesterday_subset.rename(columns={
        'naked_compete_rate_by_product': '昨日裸价竞争力',
        'real_compete_rate_by_product': '昨日实际竞争力',
        # 'naked_compete_rate_by_product_so': '昨日裸价竞争力(自营)',
        # 'real_compete_rate_by_product_so': '昨日实际竞争力(自营)',
        # 'naked_compete_rate_by_product_jd': '昨日裸价竞争力(京东)',
        # 'real_compete_rate_by_product_jd': '昨日实际竞争力(京东)',
    }, inplace=True)
    # 对今天的数据只提取 product_name 和 naked_compete_rate_by_product
    df_today_subset = df_today[['product_name', 'naked_compete_rate_by_product']].copy()
    # df_today_subset = df_today[['product_name', 'naked_compete_rate_by_product', 'naked_compete_rate_by_product_so', 'naked_compete_rate_by_product_jd']].copy()
    df_today_subset.rename(columns={
        'naked_compete_rate_by_product': '今日裸价竞争力',
        # 'naked_compete_rate_by_product_so': '今日裸价竞争力(自营)',
        # 'naked_compete_rate_by_product_jd': '今日裸价竞争力(京东)',
    }, inplace=True)
    # 将昨天和今天的数据基于 product_name 合并
    df4_by2 = pd.merge(df_yesterday_subset, df_today_subset, on='product_name', how='inner')
    df4_by2.rename(columns={'product_name': '型号'}, inplace=True)
    cols = ['昨日裸价竞争力', '昨日实际竞争力', '今日裸价竞争力']
    # cols = ['昨日裸价竞争力', '昨日实际竞争力', '今日裸价竞争力', '昨日裸价竞争力(自营)', '昨日实际竞争力(自营)', '今日裸价竞争力(自营)', '昨日裸价竞争力(京东)', '昨日实际竞争力(京东)', '今日裸价竞争力(京东)']
    df4_by2[cols] = df4_by2[cols].applymap(lambda x: '{:.1%}'.format(x) if pd.notnull(x) else x)

    # 1. 筛选“今天”和“昨天”的数据
    df_today = df4[df4['partition_date'] == current_date_str]
    df_yesterday = df4[df4['partition_date'] == date_before_str]
    # 2. 基于 product_name 和 ppv 合并两天的数据
    df_merged = pd.merge(
        df_yesterday[['product_name', 'ppv', 'zz_pre_coupon_price']],
        df_today[['product_name', 'ppv', 'zz_pre_coupon_price']],
        on=['product_name', 'ppv'],
        suffixes=('_yesterday', '_today')
    )
    # 3. 计算调价比率 (例如：-0.06 表示下降6%)
    df_merged['price_change_ratio'] = (df_merged['zz_pre_coupon_price_today'] - df_merged['zz_pre_coupon_price_yesterday']) / df_merged['zz_pre_coupon_price_yesterday']
    # 4. 定义调价幅度的分类函数
    def categorize_change(ratio):
        """
        对价格调节比率进行分类：
        - 如果比率为 0，返回 "0%"
        - 当变化率乘以 100 小于等于 -5 时，归为 "(-∞, -5%)"
        - 当变化率乘以 100 大于等于 7 时，归为 "(7%, +∞)"
        - 介于 -5% 到 7%之间，按照每 1 个百分点划分，例如：-4.2% 返回 "[-5%, -4%)"，5.3% 返回 "[5%, 6%)"
        """
        p = ratio * 100
        if ratio == 0:
            return "0%"
        elif p <= -5:
            return "(-∞, -5%)"
        elif -5 < p < 7:
            lb = math.floor(p)
            return f"[{lb}%, {lb + 1}%)"
        elif p >= 7:
            return "(7%, +∞)"
    df_merged['change_category'] = df_merged['price_change_ratio'].apply(categorize_change)
    # 5. 按 product_name 和 change_category 统计每个产品对应的 ppv 数量
    summary = df_merged.groupby(['product_name', 'change_category']).size().reset_index(name='count')
    # 6. 构建透视表：行字段为 product_name，列字段为 change_category，不存在的填充为0
    pivot_table = summary.pivot(index='product_name', columns='change_category', values='count').fillna(0)
    # 7. 确保极端区间列存在，否则补 0 值（注意后续计入总数）
    for col in ["(-∞, -5%)", "(7%, +∞)"]:
        if col not in pivot_table.columns:
            pivot_table[col] = 0
    # 8. 增加总数量列：每个产品 ppv 总数量（所有调价区间记录之和）
    pivot_table['ppv总数'] = pivot_table.sum(axis=1)
    # 9. 定义排序函数，对各区间标签进行排序：
    def sort_key(label):
        if label == "(-∞, -5%)":
            return (0, -float('inf'))
        if label == "(7%, +∞)":
            return (2, float('inf'))
        if label == "0%":
            return (1, 0)
        m = re.search(r'\[(-?\d+)%', label)
        if m:
            lb = int(m.group(1))
            if lb < 0:
                return (0, lb)
            else:
                return (2, lb)
        return (1, 0)
    # 10. 提取除了 "ppv总数" 以外的所有区间列，并按上述顺序排序
    bin_columns = [col for col in pivot_table.columns if col != 'ppv总数']
    bin_columns_sorted = sorted(bin_columns, key=sort_key)
    # 调整列的顺序：将“ppv总数”置于第一列，其余区间列按照排序顺序排列
    new_order = ['ppv总数'] + bin_columns_sorted
    pivot_table = pivot_table[new_order]
    # 11. 重置索引，并确保所有数字均为整型
    pivot_table.index.name = '型号'
    pivot_table = pivot_table.reset_index()
    pivot_table = pivot_table.astype({col: int for col in pivot_table.columns if col != '型号'})
    pivot_table.columns.name = None

    df4_final = pd.merge(df4_by2, pivot_table, on=['型号'], how='inner')
    df4_sorted = df4_final.sort_values(by='型号', ascending=True)

    # 添加邮件内容
    mail.msg_content(f"""
       <div align="center">
           <p style="color:blue; font-size:18px;"><b>🍎<全渠道> 手机不同型号的转转ppv调价明细</b></p>
       </div>
       {html_style}  <!-- 插入表格样式 -->
       <div align="center">
           {df4_sorted.to_html(index=False, escape=False)}
       </div>
    """)  # 邮件内容
    logger.info("------------Part2: df4 ended!---------------")

    # ”议价率“ 和 ”转化率“
    df3 = pd.read_sql(query3, engine)
    df3.rename(columns={
        'inspection_date': '质检日期',
        'product_brand_name': '品牌',
        'product_category_parent_name': '品类',
        'product_name': '型号',
        'submit_order_quantity': '提交订单量',
        'success_order_quantity': '成交订单数',
        'onetime_success_num_sum': '一次性成交量',
        'respect_order_num_sum': '质检订单量',
        'success_order_amount_sum': '成交订单金额',
        'success_product_amount_sum': '成交物品金额',
        'add_pass_num_sum': '加款通过量',
        'create_amount_num_sum': '提交订单金额',
        'create_product_amount_num_sum': '提交物品金额',
        'recycle_business_cost_amount_sum': '回收业务成本金额',
        'recycling_estimated_sales_sum': '回收预估销售额',
        'bargain_rate': '议价率',
        'transfer_rate': '<质检-成交>转化率'
    }, inplace=True)

    # 数据按照日期从大到小排序
    df3_sorted = df3.sort_values(by='型号', ascending=True)

    # 添加邮件内容
    mail.msg_content(f"""
       <div align="center">
           <p style="color:blue; font-size:18px;"><b>🍎手机不同型号的 “提交、质检、转换” 数据表现情况</b></p>
       </div>
       {html_style}  <!-- 插入表格样式 -->
       <div align="center">
           {df3_sorted.to_html(index=False, escape=False)}
       </div>
    """)  # 邮件内容

    logger.info("------------Part3: df3 ended!---------------")

    # ppv量，成交量，询价量 占比
    df2 = pd.read_sql(query2, engine)
    df2.rename(columns={
        'partition_date': '日期',
        'product_brand_name': '品牌',
        'product_name': '型号',
        'ppv_count': 'ppv量',
        'ppv_count_no_limit': 'ppv总量',
        'success_nums_30d': '30天成交量',
        'success_nums_30d_no_limit': '30天总成交量',
        'inquiry_nums_30d': '30天询价量',
        'inquiry_nums_30d_no_limit': '30天总询价量',
        'ppv_ratio': '30天ppv占比',
        'success_nums_ratio': '30天成交量占比',
        'inquiry_nums_ratio': '30天询价量占比',
    }, inplace=True)

    # 数据按照日期从大到小排序
    df2_sorted = df2.sort_values(by='型号', ascending=True)

    # 添加邮件内容
    mail.msg_content(f"""
        <div align="center">
            <p style="color:blue; font-size:18px;"><b>🍎新分城1组苹果各型号“ppv-询价-成交”比率<泛化到等级></b></p>
        </div>
        {html_style}  <!-- 插入表格样式 -->
        <div align="center">
            {df2_sorted.to_html(index=False, escape=False)}
        </div>
    """)  # 邮件内容
    logger.info("------------Part4: df2 ended!---------------")

    # 执行查询并获取结果
    df1 = pd.read_sql(query1, engine)
    df1.rename(columns={
        'success_date': '日期',
        'product_brand_name': '品牌',
        'product_name': '型号',
        'ppv_count': '爬取ppv量',
        'ppv_30d_count': 'ppv总量',
        'success_nums_30d': '30天成交量',
        'success_nums_30d_no_limit': '30天总成交量',
        'inquiry_nums_30d': '30天询价量',
        'inquiry_nums_30d_no_limit': '30天总询价量',
        'daily_success': '昨天成交量',
        'daily_success_no_limit': '昨天总成交量',
        'ppv_ratio': '30天ppv占比',
        'success_30d_ratio': '30天成交量占比',
        'inquiry_30d_ratio': '30天询价量占比',
        'daily_success_ratio': '昨天成交量占比'
    }, inplace=True)

    # 数据按照日期从大到小排序
    df1_sorted = df1.sort_values(by='型号', ascending=True)

    # 添加邮件内容
    mail.msg_content(f"""
        <div align="center">
            <p style="color:blue; font-size:18px;"><b>🍎新分城1组苹果各型号“ppv-询价-成交”比率</b></p>
        </div>
        {html_style}  <!-- 插入表格样式 -->
        <div align="center">
            {df1_sorted.to_html(index=False, escape=False)}
        </div>
    """)  # 邮件内容
    logger.info("------------Part5: df1 ended!---------------")

    # 附件
    file_name1 = 'collections/chosen_result_g1_{}.xlsx'.format(current_date_str)
    file_name2 = 'sku_level/sku_level_df_g1_{}.xlsx'.format(current_date_str)
    # 2025-04-09 增加2个SKU级别的调价附件
    file_name3 = 'collections/chosen_result_SKU_g1_{}.xlsx'.format(current_date_str)
    file_name4 = 'sku_level/sku_level_df_SKU_g1_{}.xlsx'.format(current_date_str)
    # 2025-05-28 增加2个放开限制条件的调价结果文件
    file_name5 = 'collections/chosen_result_g1_LET_IT_GO_{}.xlsx'.format(current_date_str)
    file_name6 = 'sku_level/sku_level_df_g1_LET_IT_GO_{}.xlsx'.format(current_date_str)

    # mail.msg_attach('{},{}'.format(file_name1, file_name2))  # 邮件附件，传入附件路径，路径多个时英文逗号隔离
    # 检查附件是否存在，只添加存在的附件
    attachments = []
    if os.path.exists(file_name1):
        attachments.append(file_name1)
    if os.path.exists(file_name2):
        attachments.append(file_name2)
    if os.path.exists(file_name3):
        attachments.append(file_name3)
    if os.path.exists(file_name4):
        attachments.append(file_name4)
    if os.path.exists(file_name5):
        attachments.append(file_name5)
    if os.path.exists(file_name6):
        attachments.append(file_name6)

    if attachments:
        # 多个附件用英文逗号分隔
        mail.msg_attach(','.join(attachments))

    mail.msg_content('<br></br>')  # 邮件内容
    mail.send()

    logger.info('mail send success')


if __name__ == '__main__':

    run_image()
